/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dnbW.uiW.dbW;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author erwin
 */
public class IdentityPathNewQueryTranslatorForUpdate {

    public static void main(String[] args) throws Exception {
        final String DB_NAME = "BD-0014-Tweak-1";

        Connection dbconnection = null;
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String url = "jdbc:sqlserver://localhost;database=" + DB_NAME + ";user=sa;password=cais";
            dbconnection = DriverManager.getConnection(url);
        } catch (Exception e) {
            System.out.println("ERROR MSG=" + "Error encountered while trying to initialize the database...");
            System.out.println(e.getMessage());
            e.printStackTrace();
            System.exit(0);
        }

        List<String> sourceList = new ArrayList<String>();
        sourceList.add("InterPro");
        Statement st = dbconnection.createStatement();

        for (String srcName : sourceList) {
            List<String> pathList = new ArrayList<String>();
            ResultSet pathSet = st.executeQuery("select pathexp from " + srcName + "_path");
            while (pathSet.next()) {
                pathList.add(pathSet.getString("pathexp"));
            }
            int max = 0;
            for (String path : pathList) {
                String[] steps = path.split("#");

                int length = (steps[steps.length - 1].contains("@") ? (steps.length - 1) : steps.length);
                max = (max < length) ? length : max;
            }
            System.out.println("height of: " + srcName + " " + max);
        }


        IdentityPathNewQueryTranslatorForUpdate engine = new IdentityPathNewQueryTranslatorForUpdate();
        String xpath = "/interprodb/interpro[pub_list/publication/year='You and I' and pub_list/publication/journal='Science']/name";

    }

    private boolean isSingleDoc(Connection dbcon, String dataSrc) {
        try {
            Statement statement = dbcon.createStatement();
            ResultSet results = statement.executeQuery(
                    "SELECT count(ID) AS TOTAL\n" +
                    "FROM " + dataSrc + "_DOCUMENT\n");
            results.next();
            if (results.getInt("TOTAL") == 1) {
                return true;
            }
        } catch (Exception ex) {
            System.out.println(ex.getMessage());
        }
        return false;
    }

    private ArrayList<PredicateModel> processPredicates(String pred, String commonPathExp) {
        ArrayList<PredicateModel> predicates = new ArrayList<PredicateModel>();

        String[] arrPred = pred.split(" and ");
        String temp = "";
        int counter = 0;
        for (int i = 0; i < arrPred.length; i++) {
            temp += arrPred[i];
            for (int j = 0; j < arrPred[i].length(); j++) {
                if (arrPred[i].substring(j, j + 1).equals("'")) {
                    counter++;
                }
            }
            if ((counter % 2) == 0) {
                PredicateModel tempPred = new PredicateModel();
                if (temp.indexOf(">=") >= 0) {
                    tempPred.setOperator(">=");
                    tempPred.setPathExp(commonPathExp + "/" + temp.substring(0, temp.indexOf(">=")));
                    tempPred.setValue(temp.substring(temp.indexOf(">=") + 1));
                } else if (temp.indexOf("<=") >= 0) {
                    tempPred.setOperator("<=");
                    tempPred.setPathExp(commonPathExp + "/" + temp.substring(0, temp.indexOf("<=")));
                    tempPred.setValue(temp.substring(temp.indexOf("<=") + 1));
                } else if (temp.indexOf("!=") >= 0) {
                    tempPred.setOperator("!=");
                    tempPred.setPathExp(commonPathExp + "/" + temp.substring(0, temp.indexOf("!=")));
                    tempPred.setValue(temp.substring(temp.indexOf("!=") + 1));
                } else if (temp.indexOf(">") >= 0) {
                    tempPred.setOperator(">");
                    tempPred.setPathExp(commonPathExp + "/" + temp.substring(0, temp.indexOf(">")));
                    tempPred.setValue(temp.substring(temp.indexOf(">") + 1));
                } else if (temp.indexOf("<") >= 0) {
                    tempPred.setOperator("<");
                    tempPred.setPathExp(commonPathExp + "/" + temp.substring(0, temp.indexOf("<")));
                    tempPred.setValue(temp.substring(temp.indexOf("<") + 1));
                } else if (temp.indexOf("=") >= 0) {
                    tempPred.setOperator("=");
                    tempPred.setPathExp(commonPathExp + "/" + temp.substring(0, temp.indexOf("=")));
                    tempPred.setValue(temp.substring(temp.indexOf("=") + 1));
                } else {
                    tempPred.setOperator("TWIG");
                    tempPred.setPathExp(commonPathExp + "/" + temp.substring(0, temp.indexOf("=")));
                    tempPred.setValue("");
                }

                if (tempPred.getPathExp().contains("@")) {
                    if (predicates.size() == 0) {
                        predicates.add(tempPred);
                    } else {
                        predicates.add(1, tempPred);
                    }
                } else {
                    predicates.add(tempPred);
                }
                counter = 0;
                temp = "";

            } else {
                temp += " and ";
            }
        }
        return predicates;
    }

    private String getAttrPathId(Connection dbcon, String dataSrc, String path) {
        String pathID = "";
        path = (path.replace("/", "#.")).substring(1) + "#";
        try {
            Statement statement = dbcon.createStatement();
            ResultSet results = statement.executeQuery(
                    "SELECT pathid, pathexp\n" +
                    "FROM " + dataSrc + "_PATH WHERE PATHEXP LIKE '" + path + "%' AND PATHEXP LIKE '%@%'");

            while (results.next()) {
                if (pathID.length() > 0) {
                    pathID += ",";
                }
                pathID += results.getString("pathid");
            }
        } catch (Exception ex) {
            System.out.println(ex.getMessage());
            return "ERROR";
        }
        return pathID;
    }

    private String getPathId(Connection dbcon, String dataSrc, String path) {
        long t1 = System.currentTimeMillis();
        String pathID = "";
        path = (path.replace("/", "#.")).substring(1) + "#";
        try {
            Statement statement = dbcon.createStatement();
            ResultSet results = statement.executeQuery(
                    "SELECT pathid, pathexp\n" +
                    "FROM " + dataSrc + "_PATH WHERE PATHEXP LIKE '" + path + "%' AND PATHEXP NOT LIKE '%@%'");
            while (results.next()) {
                if (pathID.length() > 0) {
                    pathID += ",";
                }
                pathID += results.getString("pathid");
            }
        } catch (Exception ex) {
            System.out.println(ex.getMessage());
            return "ERROR";
        }
        long t2 = System.currentTimeMillis();
        System.out.println("getPathId(): " + (t2 - t1));
        return pathID;
    }

    private long getRValue(Connection dbcon, String dataSrc, int level) {
        try {
            Statement statement = dbcon.createStatement();
            ResultSet results = statement.executeQuery(
                    "SELECT RVALUE\n" +
                    "FROM " + dataSrc + "_DOCUMENTRVALUE WHERE LEVEL = " + level);
            results.next();
            return results.getLong("RVALUE");
        } catch (Exception ex) {
            System.out.println(ex.getMessage());
            return -1;
        }
    }

    private int getNCALevel(String p1, String p2) {
        int level = 0;

        String[] P1 = p1.split("/");
        String[] P2 = p2.split("/");

        for (int i = 1; i < P1.length; i++) {
            if ((!P1[i].equals(P2[i])) || (i >= P1.length) || (i >= P2.length)) {
                break;
            } else {
                level++;
            }
        }

        return level;
    }

    public String translateSQL2(Connection dbCon, String XPath, String TempTbName, String dataSrc, String oldOperator, String oldValue) {

        StringBuilder _SELECT = new StringBuilder();
        StringBuilder _FROM = new StringBuilder();
        StringBuilder _WHERE = new StringBuilder();

        String commonPathExp = XPath.substring(0, XPath.indexOf("["));
        String Predicates = XPath.substring(XPath.indexOf("[") + 1, XPath.indexOf("]"));
        String outputPathExp = commonPathExp + XPath.substring(XPath.indexOf("]") + 1);

        ArrayList<PredicateModel> PredicateList = processPredicates(Predicates, commonPathExp);

        boolean singleDoc = isSingleDoc(dbCon, dataSrc);

        int TableCounter = 1;
        _FROM.append("FROM ");
        _WHERE.append("WHERE ");
        PredicateModel prevPred = null;
        for (int i = 0; i < PredicateList.size(); i++) {
            PredicateModel dePred = PredicateList.get(i);
            String PID = "";
            if (dePred.getPathExp().contains("@")) {
                PID = "(" + getAttrPathId(dbCon, dataSrc, dePred.getPathExp()) + ")";
                _FROM.append(dataSrc + "_ATTRIBUTE A" + TableCounter + ", " + dataSrc + "_PATHVALUE V" + TableCounter + ", ");

                if (_WHERE.toString().length() > 6) {
                    _WHERE.append("  AND ");
                }
                _WHERE.append("A" + TableCounter + ".PATHID IN " + PID + "\n\r");

                if (!dePred.getOperator().equals("TWIG")) {

                    String newValue = dePred.getValue();
                    String newOperator = dePred.getOperator();
                    float intOldVal = 0;
                    float intNewVal = 0;

                    try {
                        Float f = new Float(oldValue);
                        intOldVal = f.floatValue();
                    } catch (NumberFormatException e) {
                    }
                    try {
                        Float f = new Float(newValue);
                        intNewVal = f.floatValue();
                    } catch (NumberFormatException e) {
                    }

                    String dummy = "";

                    if (intOldVal < intNewVal) {
                        if (oldOperator.equals(">")) {
                            dummy = "  AND CAST(A" + TableCounter + ".LEAFVALUE AS FLOAT) > " + intOldVal + "\n\r";
                            _WHERE.append(dummy);
                        } else if (oldOperator.equals(">=")) {
                            dummy = "  AND CAST(A" + TableCounter + ".LEAFVALUE AS FLOAT) >= " + intOldVal + "\n\r";
                            _WHERE.append(dummy);
                        }

                        if (newOperator.equals(">=")) {
                            dummy = "  AND CAST(A" + TableCounter + ".LEAFVALUE AS FLOAT) < " + intNewVal + "\n\r";
                            _WHERE.append(dummy);
                        } else if (newOperator.equals(">")) {
                            dummy = "  AND CAST(A" + TableCounter + ".LEAFVALUE AS FLOAT) <= " + intNewVal + "\n\r";
                            _WHERE.append(dummy);
                        }
                    } else if (intOldVal > intNewVal) {
                        if (oldOperator.equals("<")) {
                            dummy = "  AND CAST(A" + TableCounter + ".LEAFVALUE AS FLOAT) < " + intOldVal + "\n\r";
                            _WHERE.append(dummy);
                        } else if (oldOperator.equals("<=")) {
                            dummy = "  AND CAST(A" + TableCounter + ".LEAFVALUE AS FLOAT) <= " + intOldVal + "\n\r";
                            _WHERE.append(dummy);
                        }

                        if (newOperator.equals("<=")) {
                            dummy = "  AND CAST(A" + TableCounter + ".LEAFVALUE AS FLOAT) > " + intNewVal + "\n\r";
                            _WHERE.append(dummy);
                        } else if (newOperator.equals("<")) {
                            dummy = "  AND CAST(A" + TableCounter + ".LEAFVALUE AS FLOAT) >= " + intNewVal + "\n\r";
                            _WHERE.append(dummy);
                        }
                    } else if (intOldVal == intNewVal) {
                        if (oldOperator.equals("<=") && newOperator.equals("=")) {
                            dummy = "  AND CAST(A" + TableCounter + ".LEAFVALUE AS FLOAT) < " + intOldVal + "\n\r";
                            _WHERE.append(dummy);
                        } else if (oldOperator.equals("<=") && newOperator.equals("<")) {
                            dummy = "  AND CAST(A" + TableCounter + ".LEAFVALUE AS FLOAT) = " + intOldVal + "\n\r";
                            _WHERE.append(dummy);
                        } else if (oldOperator.equals("!=") && newOperator.equals("<")) {
                            dummy = "  AND CAST(A" + TableCounter + ".LEAFVALUE AS FLOAT) >= " + intOldVal + "\n\r";
                            _WHERE.append(dummy);
                        } else if (oldOperator.equals(">=") && newOperator.equals("=")) {
                            dummy = "  AND CAST(A" + TableCounter + ".LEAFVALUE AS FLOAT) > " + intOldVal + "\n\r";
                            _WHERE.append(dummy);
                        } else if (oldOperator.equals(">=") && newOperator.equals(">")) {
                            dummy = "  AND CAST(A" + TableCounter + ".LEAFVALUE AS FLOAT) = " + intOldVal + "\n\r";
                            _WHERE.append(dummy);
                        } else if (oldOperator.equals("!=") && newOperator.equals(">")) {
                            dummy = "  AND CAST(A" + TableCounter + ".LEAFVALUE AS FLOAT) <= " + intOldVal + "\n\r";
                            _WHERE.append(dummy);
                        }
                    }

                }
                if (!singleDoc) {
                    _WHERE.append("  AND A" + TableCounter + ".DOCID = V" + TableCounter + ".DOCID\n\r");
                }
                _WHERE.append("  AND A" + TableCounter + ".LEAFORDER = V" + TableCounter + ".LEAFORDER\n\r");
            } else {
                PID = "(" + getPathId(dbCon, dataSrc, dePred.getPathExp()) + ")";
                _FROM.append(dataSrc + "_PATHVALUE V" + TableCounter + ", ");

                if (_WHERE.toString().length() > 6) {
                    _WHERE.append("  AND ");
                }
                _WHERE.append("V" + TableCounter + ".PATHID IN " + PID + "\n\r");

                if (!dePred.getOperator().equals("TWIG")) {
                    String newValue = dePred.getValue();
                    String newOperator = dePred.getOperator();
                    float intOldVal = 0;
                    float intNewVal = 0;

                    try {
                        Float f = new Float(oldValue);
                        intOldVal = f.floatValue();
                    } catch (NumberFormatException e) {
                    }
                    try {
                        Float f = new Float(newValue);
                        intNewVal = f.floatValue();
                    } catch (NumberFormatException e) {
                    }

                    String dummy = "";

                    if (intOldVal < intNewVal) {
                        if (oldOperator.equals(">")) {
                            dummy = "  AND CAST(V" + TableCounter + ".LEAFVALUE AS FLOAT) > " + intOldVal + "\n\r";
                            _WHERE.append(dummy);
                        } else if (oldOperator.equals(">=")) {
                            dummy = "  AND CAST(V" + TableCounter + ".LEAFVALUE AS FLOAT) >= " + intOldVal + "\n\r";
                            _WHERE.append(dummy);
                        }

                        if (newOperator.equals(">=")) {
                            dummy = "  AND CAST(V" + TableCounter + ".LEAFVALUE AS FLOAT) < " + intNewVal + "\n\r";
                            _WHERE.append(dummy);
                        } else if (newOperator.equals(">")) {
                            dummy = "  AND CAST(V" + TableCounter + ".LEAFVALUE AS FLOAT) <= " + intNewVal + "\n\r";
                            _WHERE.append(dummy);
                        }
                    } else if (intOldVal > intNewVal) {
                        if (oldOperator.equals("<")) {
                            dummy = "  AND CAST(V" + TableCounter + ".LEAFVALUE AS FLOAT) < " + intOldVal + "\n\r";
                            _WHERE.append(dummy);
                        } else if (oldOperator.equals("<=")) {
                            dummy = "  AND CAST(V" + TableCounter + ".LEAFVALUE AS FLOAT) <= " + intOldVal + "\n\r";
                            _WHERE.append(dummy);
                        }

                        if (newOperator.equals("<=")) {
                            dummy = "  AND CAST(V" + TableCounter + ".LEAFVALUE AS FLOAT) > " + intNewVal + "\n\r";
                            _WHERE.append(dummy);
                        } else if (newOperator.equals("<")) {
                            dummy = "  AND CAST(V" + TableCounter + ".LEAFVALUE AS FLOAT) >= " + intNewVal + "\n\r";
                            _WHERE.append(dummy);
                        }
                    } else if (intOldVal == intNewVal) {
                        if (oldOperator.equals("<=") && newOperator.equals("=")) {
                            dummy = "  AND CAST(V" + TableCounter + ".LEAFVALUE AS FLOAT) < " + intOldVal + "\n\r";
                            _WHERE.append(dummy);
                        } else if (oldOperator.equals("<=") && newOperator.equals("<")) {
                            dummy = "  AND CAST(V" + TableCounter + ".LEAFVALUE AS FLOAT) = " + intOldVal + "\n\r";
                            _WHERE.append(dummy);
                        } else if (oldOperator.equals("!=") && newOperator.equals("<")) {
                            dummy = "  AND CAST(V" + TableCounter + ".LEAFVALUE AS FLOAT) >= " + intOldVal + "\n\r";
                            _WHERE.append(dummy);
                        } else if (oldOperator.equals(">=") && newOperator.equals("=")) {
                            dummy = "  AND CAST(V" + TableCounter + ".LEAFVALUE AS FLOAT) > " + intOldVal + "\n\r";
                            _WHERE.append(dummy);
                        } else if (oldOperator.equals(">=") && newOperator.equals(">")) {
                            dummy = "  AND CAST(V" + TableCounter + ".LEAFVALUE AS FLOAT) = " + intOldVal + "\n\r";
                            _WHERE.append(dummy);
                        } else if (oldOperator.equals("!=") && newOperator.equals(">")) {
                            dummy = "  AND CAST(V" + TableCounter + ".LEAFVALUE AS FLOAT) <= " + intOldVal + "\n\r";
                            _WHERE.append(dummy);
                        }
                    }
                }
            }

            if (TableCounter > 1) {
                int NCALevel = getNCALevel(dePred.getPathExp(), prevPred.getPathExp());

                if (singleDoc) {
                    long RValue = getRValue(dbCon, dataSrc, (NCALevel - 1));
                    _WHERE.append("  AND V" + TableCounter + ".DEWEYORDERSUM BETWEEN V" + (TableCounter - 1) + ".DEWEYORDERSUM - " + RValue + " + 1\n\r");
                    _WHERE.append("                           AND V" + (TableCounter - 1) + ".DEWEYORDERSUM + " + RValue + " - 1\n\r");
                } else {
                    _FROM.append(dataSrc + "_DOCUMENTRVALUE R" + TableCounter + ", ");
                    _WHERE.append("  AND R" + TableCounter + ".LEVEL = " + NCALevel + "\n\r");
                    _WHERE.append("  AND R" + TableCounter + ".DOCID = V" + TableCounter + ".DOCID\n\r");
                    _WHERE.append("  AND V" + TableCounter + ".DOCID = V" + (TableCounter - 1) + ".DOCID\n\r");
                    _WHERE.append("  AND V" + TableCounter + ".DEWEYORDERSUM BETWEEN V" + (TableCounter - 1) + ".DEWEYORDERSUM - CAST(R" + TableCounter + ".RValue AS BIGINT) + 1\n\r");
                    _WHERE.append("                           AND V" + (TableCounter - 1) + ".DEWEYORDERSUM + CAST(R" + TableCounter + ".RValue AS BIGINT) - 1\n\r");
                }
            }
            prevPred = dePred;
            TableCounter++;
        }

        int NCALevel = getNCALevel(outputPathExp, prevPred.getPathExp());

        if (singleDoc) {
            long RValue = getRValue(dbCon, dataSrc, (NCALevel - 1));
            _WHERE.append("  AND PU.DEWEYORDERSUM BETWEEN V" + (TableCounter - 1) + ".DEWEYORDERSUM - " + RValue + " + 1\n\r");
            _WHERE.append("                           AND V" + (TableCounter - 1) + ".DEWEYORDERSUM + " + RValue + " - 1\n\r");
        } else {
            _FROM.append(dataSrc + "_DOCUMENTRVALUE R" + TableCounter + ", ");
            _WHERE.append("  AND R" + TableCounter + ".LEVEL = " + NCALevel + "\n\r");
            _WHERE.append("  AND R" + TableCounter + ".DOCID = PU.DOCID\n\r");
            _WHERE.append("  AND PU.DOCID = V" + (TableCounter - 1) + ".DOCID\n\r");
            _WHERE.append("  AND PU.DEWEYORDERSUM BETWEEN V" + (TableCounter - 1) + ".DEWEYORDERSUM - CAST(R" + TableCounter + ".RValue AS BIGINT) + 1\n\r");
            _WHERE.append("                           AND V" + (TableCounter - 1) + ".DEWEYORDERSUM + CAST(R" + TableCounter + ".RValue AS BIGINT) - 1\n\r");
        }
        _WHERE.append(" AND PU.DEWEYORDERSUM = X.DEWEYORDERSUM\n\r");
        _FROM.append("PathU PU");
        _SELECT.append("SELECT * ");
        String _Query = "DELETE FROM " + TempTbName + " \n\r" +
                "FROM " + TempTbName + " X \n\r" +
                "WHERE EXISTS ( \n\r" +
                _SELECT.toString() + "\n\r" + _FROM.toString() + "\n\r" + _WHERE.toString() + "\n\r" +
                ")\n\r" +
                "OPTION (FORCE ORDER)";
        return _Query;
    }

    public String translateSQL1(Connection dbCon, String XPath, String TempTbName, String dataSrc, String oldOperator, String oldValue) {

        StringBuilder _SELECT = new StringBuilder();
        StringBuilder _FROM = new StringBuilder();
        StringBuilder _WHERE = new StringBuilder();

        String commonPathExp = XPath.substring(0, XPath.indexOf("["));
        String Predicates = XPath.substring(XPath.indexOf("[") + 1, XPath.indexOf("]"));
        String outputPathExp = commonPathExp + XPath.substring(XPath.indexOf("]") + 1);

        ArrayList<PredicateModel> PredicateList = processPredicates(Predicates, commonPathExp);

        boolean singleDoc = isSingleDoc(dbCon, dataSrc);

        int TableCounter = 1;
        _FROM.append("FROM ");
        _WHERE.append("WHERE ");
        PredicateModel prevPred = null;
        for (int i = 0; i < PredicateList.size(); i++) {
            PredicateModel dePred = PredicateList.get(i);
            String PID = "";
            if (dePred.getPathExp().contains("@")) {
                PID = "(" + getAttrPathId(dbCon, dataSrc, dePred.getPathExp()) + ")";
                _FROM.append(dataSrc + "_ATTRIBUTE A" + TableCounter + ", " + dataSrc + "_PATHVALUE V" + TableCounter + ", ");

                if (_WHERE.toString().length() > 6) {
                    _WHERE.append("  AND ");
                }
                _WHERE.append("A" + TableCounter + ".PATHID IN " + PID + "\n\r");

                if (!dePred.getOperator().equals("TWIG")) {

                    String newValue = dePred.getValue();
                    String newOperator = dePred.getOperator();
                    float intOldVal = 0;
                    float intNewVal = 0;

                    try {
                        Float f = new Float(oldValue);
                        intOldVal = f.floatValue();
                    } catch (NumberFormatException e) {
                    }
                    try {
                        Float f = new Float(newValue);
                        intNewVal = f.floatValue();
                    } catch (NumberFormatException e) {
                    }

                    String dummy = "";

                    if (intOldVal < intNewVal) {
                        if (oldOperator.equals("<")) {
                            dummy = "  AND CAST(A" + TableCounter + ".LEAFVALUE AS FLOAT) >= " + intOldVal + "\n\r";
                            _WHERE.append(dummy);
                        } else if (oldOperator.equals("<=")) {
                            dummy = "  AND CAST(A" + TableCounter + ".LEAFVALUE AS FLOAT) > " + intOldVal + "\n\r";
                            _WHERE.append(dummy);
                        }

                        if (newOperator.equals("<=")) {
                            dummy = "  AND CAST(A" + TableCounter + ".LEAFVALUE AS FLOAT) <= " + intNewVal + "\n\r";
                            _WHERE.append(dummy);
                        } else if (newOperator.equals("<")) {
                            dummy = "  AND CAST(A" + TableCounter + ".LEAFVALUE AS FLOAT) < " + intNewVal + "\n\r";
                            _WHERE.append(dummy);
                        } else if (newOperator.equals("!=")) {
                            dummy = "  AND CAST(A" + TableCounter + ".LEAFVALUE AS FLOAT) <> " + intNewVal + "\n\r";
                            _WHERE.append(dummy);
                        }
                    } else if (intOldVal > intNewVal) {
                        if (oldOperator.equals(">")) {
                            dummy = "  AND CAST(A" + TableCounter + ".LEAFVALUE AS FLOAT) <= " + intOldVal + "\n\r";
                            _WHERE.append(dummy);
                        } else if (oldOperator.equals(">=")) {
                            dummy = "  AND CAST(A" + TableCounter + ".LEAFVALUE AS FLOAT) < " + intOldVal + "\n\r";
                            _WHERE.append(dummy);
                        }

                        if (newOperator.equals(">=")) {
                            dummy = "  AND CAST(A" + TableCounter + ".LEAFVALUE AS FLOAT) >= " + intNewVal + "\n\r";
                            _WHERE.append(dummy);
                        } else if (newOperator.equals(">")) {
                            dummy = "  AND CAST(A" + TableCounter + ".LEAFVALUE AS FLOAT) > " + intNewVal + "\n\r";
                            _WHERE.append(dummy);
                        } else if (newOperator.equals("!=")) {
                            dummy = "  AND CAST(A" + TableCounter + ".LEAFVALUE AS FLOAT) <> " + intNewVal + "\n\r";
                            _WHERE.append(dummy);
                        }
                    } else if (intOldVal == intNewVal) {
                        if (oldOperator.equals("<") && newOperator.equals("<=")) {
                            dummy = "  AND CAST(A" + TableCounter + ".LEAFVALUE AS FLOAT) = " + intOldVal + "\n\r";
                            _WHERE.append(dummy);
                        } else if (oldOperator.equals("<") && newOperator.equals("!=")) {
                            dummy = "  AND CAST(A" + TableCounter + ".LEAFVALUE AS FLOAT) > " + intOldVal + "\n\r";
                            _WHERE.append(dummy);
                        } else if (oldOperator.equals("=") && newOperator.equals("<=")) {
                            dummy = "  AND CAST(A" + TableCounter + ".LEAFVALUE AS FLOAT) < " + intOldVal + "\n\r";
                            _WHERE.append(dummy);
                        } else if (oldOperator.equals(">") && newOperator.equals(">=")) {
                            dummy = "  AND CAST(A" + TableCounter + ".LEAFVALUE AS FLOAT) = " + intOldVal + "\n\r";
                            _WHERE.append(dummy);
                        } else if (oldOperator.equals(">") && newOperator.equals("!=")) {
                            dummy = "  AND CAST(A" + TableCounter + ".LEAFVALUE AS FLOAT) < " + intOldVal + "\n\r";
                            _WHERE.append(dummy);
                        } else if (oldOperator.equals("=") && newOperator.equals(">=")) {
                            dummy = "  AND CAST(A" + TableCounter + ".LEAFVALUE AS FLOAT) > " + intOldVal + "\n\r";
                            _WHERE.append(dummy);
                        }
                    }

                }
                if (!singleDoc) {
                    _WHERE.append("  AND A" + TableCounter + ".DOCID = V" + TableCounter + ".DOCID\n\r");
                }
                _WHERE.append("  AND A" + TableCounter + ".LEAFORDER = V" + TableCounter + ".LEAFORDER\n\r");
            } else {
                PID = "(" + getPathId(dbCon, dataSrc, dePred.getPathExp()) + ")";
                _FROM.append(dataSrc + "_PATHVALUE V" + TableCounter + ", ");

                if (_WHERE.toString().length() > 6) {
                    _WHERE.append("  AND ");
                }
                _WHERE.append("V" + TableCounter + ".PATHID IN " + PID + "\n\r");

                if (!dePred.getOperator().equals("TWIG")) {
                    String newValue = dePred.getValue();
                    String newOperator = dePred.getOperator();
                    float intOldVal = 0;
                    float intNewVal = 0;

                    try {
                        Float f = new Float(oldValue);
                        intOldVal = f.floatValue();
                    } catch (NumberFormatException e) {
                    }
                    try {
                        Float f = new Float(newValue);
                        intNewVal = f.floatValue();
                    } catch (NumberFormatException e) {
                    }

                    String dummy = "";

                    if (intOldVal < intNewVal) {
                        if (oldOperator.equals("<")) {
                            dummy = "  AND CAST(V" + TableCounter + ".LEAFVALUE AS FLOAT) >= " + intOldVal + "\n\r";
                            _WHERE.append(dummy);
                        } else if (oldOperator.equals("<=")) {
                            dummy = "  AND CAST(V" + TableCounter + ".LEAFVALUE AS FLOAT) > " + intOldVal + "\n\r";
                            _WHERE.append(dummy);
                        }

                        if (newOperator.equals("<=")) {
                            dummy = "  AND CAST(V" + TableCounter + ".LEAFVALUE AS FLOAT) <= " + intNewVal + "\n\r";
                            _WHERE.append(dummy);
                        } else if (newOperator.equals("<")) {
                            dummy = "  AND CAST(V" + TableCounter + ".LEAFVALUE AS FLOAT) < " + intNewVal + "\n\r";
                            _WHERE.append(dummy);
                        } else if (newOperator.equals("!=")) {
                            dummy = "  AND CAST(V" + TableCounter + ".LEAFVALUE AS FLOAT) <> " + intNewVal + "\n\r";
                            _WHERE.append(dummy);
                        }
                    } else if (intOldVal > intNewVal) {
                        if (oldOperator.equals(">")) {
                            dummy = "  AND CAST(V" + TableCounter + ".LEAFVALUE AS FLOAT) <= " + intOldVal + "\n\r";
                            _WHERE.append(dummy);
                        } else if (oldOperator.equals(">=")) {
                            dummy = "  AND CAST(V" + TableCounter + ".LEAFVALUE AS FLOAT) < " + intOldVal + "\n\r";
                            _WHERE.append(dummy);
                        }

                        if (newOperator.equals(">=")) {
                            dummy = "  AND CAST(V" + TableCounter + ".LEAFVALUE AS FLOAT) >= " + intNewVal + "\n\r";
                            _WHERE.append(dummy);
                        } else if (newOperator.equals(">")) {
                            dummy = "  AND CAST(V" + TableCounter + ".LEAFVALUE AS FLOAT) > " + intNewVal + "\n\r";
                            _WHERE.append(dummy);
                        } else if (newOperator.equals("!=")) {
                            dummy = "  AND CAST(V" + TableCounter + ".LEAFVALUE AS FLOAT) <> " + intNewVal + "\n\r";
                            _WHERE.append(dummy);
                        }
                    } else if (intOldVal == intNewVal) {
                        if (oldOperator.equals("<") && newOperator.equals("<=")) {
                            dummy = "  AND CAST(V" + TableCounter + ".LEAFVALUE AS FLOAT) = " + intOldVal + "\n\r";
                            _WHERE.append(dummy);
                        } else if (oldOperator.equals("<") && newOperator.equals("!=")) {
                            dummy = "  AND CAST(V" + TableCounter + ".LEAFVALUE AS FLOAT) > " + intOldVal + "\n\r";
                            _WHERE.append(dummy);
                        } else if (oldOperator.equals("=") && newOperator.equals("<=")) {
                            dummy = "  AND CAST(V" + TableCounter + ".LEAFVALUE AS FLOAT) < " + intOldVal + "\n\r";
                            _WHERE.append(dummy);
                        } else if (oldOperator.equals(">") && newOperator.equals(">=")) {
                            dummy = "  AND CAST(V" + TableCounter + ".LEAFVALUE AS FLOAT) = " + intOldVal + "\n\r";
                            _WHERE.append(dummy);
                        } else if (oldOperator.equals(">") && newOperator.equals("!=")) {
                            dummy = "  AND CAST(V" + TableCounter + ".LEAFVALUE AS FLOAT) < " + intOldVal + "\n\r";
                            _WHERE.append(dummy);
                        } else if (oldOperator.equals("=") && newOperator.equals(">=")) {
                            dummy = "  AND CAST(V" + TableCounter + ".LEAFVALUE AS FLOAT) > " + intOldVal + "\n\r";
                            _WHERE.append(dummy);
                        }
                    }
                }
            }

            if (TableCounter > 1) {
                int NCALevel = getNCALevel(dePred.getPathExp(), prevPred.getPathExp());

                if (singleDoc) {
                    long RValue = getRValue(dbCon, dataSrc, (NCALevel - 1));
                    _WHERE.append("  AND V" + TableCounter + ".DEWEYORDERSUM BETWEEN V" + (TableCounter - 1) + ".DEWEYORDERSUM - " + RValue + " + 1\n\r");
                    _WHERE.append("                           AND V" + (TableCounter - 1) + ".DEWEYORDERSUM + " + RValue + " - 1\n\r");
                } else {
                    _FROM.append(dataSrc + "_DOCUMENTRVALUE R" + TableCounter + ", ");
                    _WHERE.append("  AND R" + TableCounter + ".LEVEL = " + NCALevel + "\n\r");
                    _WHERE.append("  AND R" + TableCounter + ".DOCID = V" + TableCounter + ".DOCID\n\r");
                    _WHERE.append("  AND V" + TableCounter + ".DOCID = V" + (TableCounter - 1) + ".DOCID\n\r");
                    _WHERE.append("  AND V" + TableCounter + ".DEWEYORDERSUM BETWEEN V" + (TableCounter - 1) + ".DEWEYORDERSUM - CAST(R" + TableCounter + ".RValue AS BIGINT) + 1\n\r");
                    _WHERE.append("                           AND V" + (TableCounter - 1) + ".DEWEYORDERSUM + CAST(R" + TableCounter + ".RValue AS BIGINT) - 1\n\r");
                }
            }
            prevPred = dePred;
            TableCounter++;
        }

        int NCALevel = getNCALevel(outputPathExp, prevPred.getPathExp());

        if (singleDoc) {
            long RValue = getRValue(dbCon, dataSrc, (NCALevel - 1));
            _WHERE.append("  AND PU.DEWEYORDERSUM BETWEEN V" + (TableCounter - 1) + ".DEWEYORDERSUM - " + RValue + " + 1\n\r");
            _WHERE.append("                           AND V" + (TableCounter - 1) + ".DEWEYORDERSUM + " + RValue + " - 1\n\r");
        } else {
            _FROM.append(dataSrc + "_DOCUMENTRVALUE R" + TableCounter + ", ");
            _WHERE.append("  AND R" + TableCounter + ".LEVEL = " + NCALevel + "\n\r");
            _WHERE.append("  AND R" + TableCounter + ".DOCID = PU.DOCID\n\r");
            _WHERE.append("  AND PU.DOCID = V" + (TableCounter - 1) + ".DOCID\n\r");
            _WHERE.append("  AND PU.DEWEYORDERSUM BETWEEN V" + (TableCounter - 1) + ".DEWEYORDERSUM - CAST(R" + TableCounter + ".RValue AS BIGINT) + 1\n\r");
            _WHERE.append("                           AND V" + (TableCounter - 1) + ".DEWEYORDERSUM + CAST(R" + TableCounter + ".RValue AS BIGINT) - 1\n\r");
        }
        _FROM.append("PathU PU");
        _SELECT.append("SELECT DISTINCT PU.LeafOrder, PU.DeweyOrderSum, PU.DocId ");
        String _Query = "INSERT INTO " + TempTbName + " \n\r" +
                _SELECT.toString() + "\n\r" + _FROM.toString() + "\n\r" + _WHERE.toString() + "\n\r" +
                "OPTION (FORCE ORDER)";
        return _Query;
    }
}
